#!/bin/bash

#输出路径
output_path=/db_backup/database_delete
if [ ! -d $output_path ]
then
        mkdir -p $output_path
fi

#if [ $# -ne 5 ]
#then
#        echo "usage: $0 <db_ip> <db_port> <db_username> <db_password> <db_database>"
#        exit
#fi

#删除指定日期之前的GIS数据，默认保留6个月（186天）（需配置）
DATE_Ymd=$(date -d "-186 day" +%Y%m%d)
#echo $DATE_Ymd

#数据库配置信息
#db_ip=$1                    # 172.16.112.21
#db_port=$2                  # 3306
#db_username=$3              # root
#db_password=$4              # airport
#db_database=$5              # db_oil_pvg


#配置通用命令
mysql_org="mysql -h$db_ip -P$db_port -u$db_username -p$db_password -D$db_database -N"

#脚本执行结果输出日志
FILE_LOG_INFO="$output_path/$db_database-$(date '+%Y%m%d%H%M%S').log"

#将需要删除的分区表名称查找出来
echo -e "\n正在查找服务器[$db_ip]上指定日期[$DATE_Ymd]之前需要删除的分区表...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
sql="SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME 
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA LIKE 'db_oil_%' AND PARTITION_NAME LIKE 'p%' AND PARTITION_NAME<'p$DATE_Ymd'
ORDER BY TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME"
table_info=$($mysql_org -e"$sql")
#echo "$table_info"
echo -e "查找服务器[$db_ip]上指定日期[$DATE_Ymd]之前需要删除的分区表结束...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO

echo -e "\n开始删除服务器[$db_ip]上指定日期[$DATE_Ymd]之前需要删除的分区表...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
while read TABLE_SCHEMA TABLE_NAME PARTITION_NAME
do
	if [[ $PARTITION_NAME != "" && "$PARTITION_NAME" != "NULL" ]]
	then
		echo -e "\n正在删除数据库[$TABLE_SCHEMA]表[$TABLE_NAME]的分区表[$PARTITION_NAME]...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
		sql_delete="ALTER TABLE $TABLE_SCHEMA.$TABLE_NAME DROP PARTITION $PARTITION_NAME"
		echo $sql_delete
		$mysql_org -e "$sql_delete"
                if [ $? -ne 0 ]
                then
                	echo "删除数据库[$TABLE_SCHEMA]表[$TABLE_NAME]的分区表[$PARTITION_NAME]失败...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
                fi
		echo -e "数据库[$TABLE_SCHEMA]表[$TABLE_NAME]的分区表[$PARTITION_NAME]已经删除...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
	fi
done< <(echo "$table_info")
echo -e "\n服务器[$db_ip]上指定日期[$DATE_Ymd]之前需要删除的分区表已经全部删除完毕...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
